package com.smart.base.tools.generator.pojo.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.smart.base.tools.generator.pojo.consts.GenerateConsts;
import com.smart.base.tools.generator.pojo.model.ColumnMeta;
import com.smart.base.tools.generator.pojo.model.TableMeta;


/**
 * @ClassName：AnalysisDB @Description：
 * @author：shisongbin
 * @date：2016年5月24日 下午4:43:02
 */
public class AnalysisDB {

	/**
	 * 读取表列表
	 * 
	 * @return
	 */
	public static final List<TableMeta> readDB(String[] tables) {
		List<TableMeta> list = new ArrayList<TableMeta>();
		String sql = "SELECT TABLE_NAME ,TABLE_COMMENT FROM	information_schema.TABLES WHERE TABLE_SCHEMA = '" + GenerateConsts.DB_NAME + "'";
		if (tables != null && tables.length > 0) {
			sql += " AND TABLE_NAME IN (";
			StringBuffer sb = new StringBuffer();
			for (String table : tables) {
				sb.append(",'" + table + "'");
			}
			sql += (sb.toString().replaceFirst(",", "") + " )");
		}
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = DBHandler.createConnection();
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				TableMeta tm = new TableMeta();
				tm.setSchemaName(GenerateConsts.DB_NAME);
				tm.setTableName(rs.getString("TABLE_NAME"));
				tm.setComment(rs.getString("TABLE_COMMENT"));
				list.add(tm);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				ps.close();
				DBHandler.close(conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
	}

	/**
	 * 填充字段信息
	 * 
	 * @param tables
	 */
	public static final void readTables(List<TableMeta> tables) {
		Connection conn = null;
		if (tables != null) {
			conn = DBHandler.createConnection();
			for (TableMeta tm : tables) {
				readTable(tm, conn);
			}
		}
		DBHandler.close(conn);
	}

	/**
	 * 读取表结构
	 * 
	 * @param table
	 */
	private static final void readTable(TableMeta table, Connection conn) {
		List<ColumnMeta> list = new ArrayList<ColumnMeta>();
		String sql = "SELECT COLUMN_NAME ,ORDINAL_POSITION ,IS_NULLABLE ,COLUMN_DEFAULT ,COLUMN_TYPE ,COLUMN_KEY ,EXTRA ,COLUMN_COMMENT "
				+ " FROM information_schema.columns WHERE table_schema ='" + GenerateConsts.DB_NAME + "' AND table_name = '" + table.getTableName() + "'";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				ColumnMeta fm = new ColumnMeta();

				fm.setColumnName(rs.getString("COLUMN_NAME"));
				fm.setOrdinalPosition(rs.getString("ORDINAL_POSITION"));
				fm.setIsNullable(rs.getString("IS_NULLABLE"));
				fm.setColumnDefault(rs.getString("COLUMN_DEFAULT"));
				fm.setColumnType(rs.getString("COLUMN_TYPE"));
				fm.setColumnKey(rs.getString("COLUMN_KEY"));
				fm.setExtra(rs.getString("EXTRA"));
				fm.setColumnComment(rs.getString("COLUMN_COMMENT"));

				if ("PRI".equalsIgnoreCase(fm.getColumnKey())) {
					table.setPrimaryKey(fm.getColumnName());
				}
				list.add(fm);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				ps.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		table.setColumns(list);
	}
}
